package com.dudu.dao;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import core.CoreDao;
import core.db.DBConnectionUtil;

public class BillDao {
	
	public static  Logger logger = Logger.getLogger(BillDao.class);
	
    public JSONObject queryBillDetailsById(CoreDao dao,String sessionId,String accountIdentifer) {
		
		Connection con = null;
		List<JSONObject> list = null;
		JSONObject result=null;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(  
					"select meet1.isAccepted,meet1.create_time as createtime,meet1.call_identifier,meet1.caller,meet1.phones,group_concat(cb.callee) as mem,  group_concat(CAST(cb.time AS CHAR)) as times, meet1.duration as cost ");
			sql.append(" from (SELECT meet.is_accepted isAccepted,meet.create_time,meet.session_id call_identifier,meet.caller,meet.called phones,meet.duration FROM t_meet_info meet where ");
			sql.append(" (meet.ability_type not in('VoiceNotice3','AudeoSend') or ability_type is null) and meet.cust_account=? and meet.session_id=? ) ");   
			sql.append(
					"  meet1 left join t_meet_ims_callback cb on cb.session_id = meet1.call_identifier ");
			sql.append(" group by meet1.call_identifier  order by meet1.create_time desc ");  
			logger.info("queryBillDetailsById=="+sql.toString());
			list = dao.serviceJsonSql(con, sql.toString(),new Object[]{accountIdentifer,sessionId});  
			if(list!=null&&list.size()>0){
				result=list.get(0);
			}			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
    
    
    public List<JSONObject> queryBillList(CoreDao dao,String startTime,String endTime,String accountId,String user_id,String ability_type,String app_key,String isAccpeted) {
		Connection con = null;
		List<JSONObject> list = null;
		Object[] params=null;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			List paramters=new ArrayList();
			StringBuffer sql = new StringBuffer(  
					"select meet1.ability_type,meet1.create_time as createtime,meet1.call_identifier,meet1.caller,meet1.phones,group_concat(cb.callee) as mem,  group_concat(CAST(cb.time AS CHAR)) as times, meet1.duration as cost ");
			sql.append(" from (SELECT meet.create_time,	meet.session_id call_identifier,meet.caller,meet.called phones,meet.duration,meet.ability_type ");
			sql.append(" FROM t_meet_info meet where 1=1 ");
			if(!StringUtils.isEmpty(app_key)){
				sql.append(" and meet.app_key=? ");
				paramters.add(app_key);
			}
			if(!StringUtils.isEmpty(accountId)){
				sql.append(" and meet.cust_account=? ");
				paramters.add(accountId);
			}
			if(!StringUtils.isEmpty(user_id)){
				sql.append(" and meet.user_identify=? ");
				paramters.add(user_id);
			}
			if(!StringUtils.isEmpty(isAccpeted)){
				sql.append(" and meet.is_accepted=? ");
				paramters.add(isAccpeted);
			}
			if(!StringUtils.isEmpty(startTime)){
				sql.append(" and meet.create_time>=? ");
				paramters.add(startTime);
			}
			if(!StringUtils.isEmpty(endTime)){
				sql.append(" and meet.create_time<=? ");
				paramters.add(endTime);
			}
			if(!StringUtils.isEmpty(ability_type)){
				if(ability_type.indexOf(",")>-1){
					String[] types=ability_type.split(",");
					sql.append(" and meet.ability_type in (?");
					paramters.add(types[0]);
					for (int i = 1; i < types.length; i++) {
						sql.append(",?");
						paramters.add(types[i]);
					}
					sql.append(") ");
				}else{
					sql.append(" and meet.ability_type=? ");
					paramters.add(ability_type);
				}
			}		
			sql.append(
					" ) meet1 left join t_meet_ims_callback cb on cb.session_id = meet1.call_identifier where cb.ability_type !='CallRecorder' ");
			sql.append(" group by meet1.call_identifier  order by meet1.create_time desc ");
			params=paramters.toArray();
			logger.info("queryBillList=="+sql.toString());
			list = dao.serviceJsonSql(con, sql.toString(),params);  
			return list;

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}

		return list;
	}
	
    /**
     * 根绝sessionId查询语音通知的发送结果
     * @param dao
     * @param sessionId
     * @return
     */
    public JSONObject querySmsBillDetailsById(CoreDao dao,String sessionId) {
		
		Connection con = null;
		List<JSONObject> list = null;
		JSONObject result=null;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(" SELECT i.duration as 'times', s.vSessionsId AS 'sessionId', s.vCallState AS 'callState', " + 
					 " CASE " +
					 " 		WHEN s.vReason LIKE '%-21%' THEN '呼叫被拒接' " +
					 "		WHEN s.vReason LIKE '%-19%' THEN '振铃无应答' " +
					 "		WHEN s.vReason LIKE '%-17%' THEN '用户忙' " +
					 "		WHEN s.vReason LIKE '%-16%' THEN '主叫结束呼叫或呼叫被拦截' " +
					 "		WHEN s.vReason LIKE '%-1' THEN '空号' " +
					 "		ELSE '用户暂时无法接通' " +
					 " END AS 'reason' ");
			sql.append(" FROM t_call_status s ");
			sql.append(" LEFT JOIN t_meet_ims i ON s.vSessionsId = i.call_identifier ");		
			sql.append(" WHERE s.vSessionsId = ? ");   
			sql.append("  ORDER BY s.vStateTime DESC LIMIT 1 ");
			logger.info("queryBillDetailsById=="+sql.toString());
			list = dao.serviceJsonSql(con, sql.toString(),new Object[]{ sessionId});  
			if(list!=null&&list.size()>0){
				result=list.get(0);
			}			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
    
    /**
     * 根绝sessionId查询语音通知的发送结果
     * @param dao
     * @param sessionId
     * @return
     */
    public JSONObject querySmsBillDetailsByPhoneAndVoiceId(CoreDao dao, String voiceId, String phone) {
		
		Connection con = null;
		List<JSONObject> list = null;
		JSONObject result=null;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(" SELECT IFNULL(c.time, 0) as 'times', s.vSessionsId AS 'sessionId', s.vCallState AS 'callState', " + 
					 " CASE " +
					 " 		WHEN s.vReason LIKE '%-21%' THEN '呼叫被拒接' " +
					 "		WHEN s.vReason LIKE '%-19%' THEN '振铃无应答' " +
					 "		WHEN s.vReason LIKE '%-17%' THEN '用户忙' " +
					 "		WHEN s.vReason LIKE '%-16%' THEN '主叫结束呼叫或呼叫被拦截' " +
					 "		WHEN s.vReason LIKE '%-1' THEN '空号' " +
					 "		ELSE '用户暂时无法接通' " +
					 " END AS 'reason' ");
			sql.append(" FROM t_call_status s ");
			sql.append(" LEFT JOIN t_meet_ims_callback c ON s.vSessionsId = c.session_id ");	
			sql.append(" LEFT JOIN t_send_notice n ON s.vSessionsId = n.session_id ");	
			sql.append(" WHERE  n.callee=? AND n.meet_ims_id=? ");   
			sql.append("  ORDER BY s.vStateTime DESC LIMIT 1 ");
			logger.info("queryBillDetailsById=="+sql.toString());
			list = dao.serviceJsonSql(con, sql.toString(),new Object[]{ phone, voiceId});  
			if(list!=null&&list.size()>0){
				result=list.get(0);
			}			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
    
    /**
     * 根绝手机号查询两方通话记录
     * @param dao
     * @param phoneNum
     * @return
     */
    public List<JSONObject> queryDialBillDetailsByPhoneNum(CoreDao dao, String phoneNum,String called) {
		
		Connection con = null;
		List<JSONObject> result = null;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(256);
			sql.append(" SELECT DATE_FORMAT(c.beginTime, '%m月%d日') AS 'createTimeDay', DATE_FORMAT(c.beginTime, '%H:%i') AS 'createTimeHour', ");
			sql.append(" c.Callee AS 'callee', c.Caller AS 'caller' , c.time ");
			sql.append(" FROM t_meet_ims_callback c ");
			sql.append(" WHERE  c.ability_type LIKE '%Dial%' ");   
			sql.append(" AND ((c.Caller LIKE ? and c.Callee LIKE ?) or (c.Caller LIKE ? and c.Callee LIKE ?))"); 
			sql.append(" ORDER BY c.beginTime DESC LIMIT 30; ");
			logger.info("queryBillDetailsById=="+sql.toString());
			result = dao.serviceJsonSql(con, sql.toString(),new Object[]{ "%" + phoneNum,  "%" + called,"%" + called,"%" + phoneNum});  			
		} catch (Exception ex) {
			ex.printStackTrace(); 
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
    
    /**
     * 根绝手机号查询两方通话记录
     * @param dao
     * @param phoneNum
     * @return
     */
    public List<JSONObject> queryAppCardpay(CoreDao dao, String appKey) {
		
		Connection con = null;
		List<JSONObject> result = null;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			String sql = " SELECT * FROM t_app_cardpay WHERE app_key=? "; 
			logger.info("queryBillDetailsById=="+sql.toString());
			result = dao.serviceJsonSql(con, sql.toString(),new Object[]{ appKey });  			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
    
    /**
     * 充值配置表t_app_cardpay初始化应用数据
     * @param dao
     * @param appId
     * @return
     */
    public int insertAppCardpay(CoreDao dao, String appKey) {
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"INSERT INTO t_app_cardpay(app_key, company_code, product_code, region_code)");
			sql.append("VALUES(?, '10', '01', '000')");

			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { 
						appKey
					});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

}
